/**
 * @ClassName: SysProductDao
 * @author linsen
 * @date 2016-10-11
 */
package com.fzrskj.cloud.free.sys.dao;

import com.fzrskj.cloud.free.core.enums.DicParamEnum;
import com.fzrskj.cloud.free.core.enums.ProductTypeEnum;
import com.fzrskj.cloud.free.core.tool.CObjectTool;
import com.fzrskj.cloud.free.core.utils.CloudContextUtils;
import com.fzrskj.cloud.free.core.utils.CloudDbDrvUtils;
import com.fzrskj.cloud.free.sys.bean.SysProductView;
import com.fzrskj.cloud.free.sys.bean.SysProduct;
import ms.core.consts.SysErr;
import ms.core.db.IDbDrv;
import ms.core.object.CObject;
import ms.core.tool.StrTool;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.collections.map.HashedMap;

import java.util.*;

public class SysProductDao extends CObject {

    private IDbDrv db = null;

    public SysProductDao() {
        db = CloudDbDrvUtils.getDbUtilsDrv();
    }

    @Override
    public void dispose() {
        db.closeConnection();
    }

    //to add new record to database by bean
    public int addSysProduct(SysProduct obj) {

        obj.setAgentId(CloudContextUtils.getAgentId());

        String sql = "insert into sys_product(createTime,listId,productClass,productCode,productName," +
                "saleType,status,userGradeSale,remark,subClass," +
                "recommend,regType,agentId) " +
                "values(?,?,?,?,?," +
                "?,?,?,?,?," +
                "?,?,?);";
        Object[] prm = {obj.getCreateTime(), obj.getListId(), obj.getProductClass(), obj.getProductCode(), obj.getProductName(),
                obj.getSaleType(), obj.getStatus(), obj.getUserGradeSale(), obj.getRemark(), obj.getSubClass(),
                obj.getRecommend(), obj.getRegType(), obj.getAgentId()};

        int ret = db.executeSql(sql, prm);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to delete table record by cdt
    public int delSysProduct(Map<String, Object> cdt) {

        cdt.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.deleteRecord("sys_product", cdt);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    //to modify table record by values and condition
    public int modSysProduct(Map<String, Object> values, Map<String, Object> cdt) {

        cdt.put("agentId", CloudContextUtils.getAgentId());

        int ret = db.modifyRecord("sys_product", values, cdt);
        String code = db.getErrCode();
        this.setErrCode(code);
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }

        return ret;
    }

    public int querySysProductCount(Map<String, Object> cdt) {
        return this.querySysProductCount(cdt, new HashMap<>());
    }

    //to query SysProduct record count
    public int querySysProductCount(Map<String, Object> cdt, Map<String, Object> filter) {

        cdt.put("agentId", CloudContextUtils.getAgentId());

        StringBuilder sb = new StringBuilder("select count(*) from sys_product where 1=1 ");
        List<Object> params = new ArrayList<>();
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if(c.getKey().equals("productName")){
                sb.append(" and " + c.getKey() + " like ?");
                params.add("%"+c.getValue()+"%");
            }else {
                sb.append(" and " + c.getKey() + "=?");
                params.add(c.getValue());
            }
        }

        if (filter.containsKey("productName")) {
            sb.append(" and productName like ? ");
            params.add("%" + MapUtils.getString(filter, "productName") + "%");
        }
        if (filter.containsKey("productCode")) {
            sb.append(" and productCode like ? ");
            params.add("%" + MapUtils.getString(filter, "productCode") + "%");
        }
        if (filter.containsKey("status")) {
            sb.append(" and status = ? ");
            params.add(filter.get("status"));
        }

        Object queryResult = db.queryFieldValue(sb.toString(), Integer.class, params, 1);
        if(queryResult == null){
            return 0;
        }
        int ret = Integer.valueOf(String.valueOf(queryResult));
        this.setErrCode(db.getErrCode());
        return ret;

//        cdt.put("agentId", CloudContextUtils.getAgentId());
//        int ret = db.queryRecordCount("sys_product", cdt);
//        this.setErrCode(db.getErrCode());
//        return ret;
    }

    //自定义方法(非生成)
    public List<SysProductView> getProductList(Map<String, Object> cdt, int page, int pagesize) {
        return this.getProductList(cdt, page, pagesize, new HashedMap());
    }
    //获取产品列表
    public List<SysProductView> getProductList(Map<String, Object> cdt, int page, int pagesize, Map<String, Object> filter) {
        cdt.put("sp.agentId", CloudContextUtils.getAgentId());
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select *,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='productclass' and t.value= sp.productClass) as productClassName ");
        sb.append(",case when sp.productClass='diy' then (select m.categoryName from sys_product_diycat m ")
                .append(" where m.categoryCode=sp.subClass) else '' end as subClassName ");
        sb.append(" from sys_product sp where 1=1 ");
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            if(c.getKey().equals("productName")){
                sb.append(" and " + c.getKey() + " like ?");
                params.add("%"+c.getValue()+"%");
            }else {
                sb.append(" and " + c.getKey() + "=?");
                params.add(c.getValue());
            }
        }
        if (filter.containsKey("productName")) {
            sb.append(" and sp.productName like ? ");
            params.add("%" + MapUtils.getString(filter, "productName") + "%");
        }
        if (filter.containsKey("productCode")) {
            sb.append(" and sp.productCode like ? ");
            params.add("%" + MapUtils.getString(filter, "productCode") + "%");
        }
        if (filter.containsKey("status")) {
            sb.append(" and sp.status = ? ");
            params.add(filter.get("status"));
        }

        sb.append(" order by sp.listId,sp.id asc ");

        if (page > 0 && pagesize > 0) {
            sb.append(String.format(" limit %d,%d", (page - 1) * pagesize, pagesize));
        }

        List<SysProductView> list = db.queryForBeans(sb.toString(), SysProductView.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return list;
    }

    //查询产品
    public SysProductView querySysProduct(Map<String, Object> cdt) {
        if (!cdt.containsKey("sp.agentId")) {
            cdt.put("sp.agentId", CloudContextUtils.getAgentId());
        }
        StringBuilder sb = new StringBuilder("select *,");
        sb.append("(select t.description from dic_paramindex n,dic_paramitem t where t.paramId=n.paramEName  and n.paramEName='productclass' and t.value= sp.productClass) as productClassName ");
        sb.append(",case when sp.productClass='diy' then (select m.categoryName from sys_product_diycat m ");
        sb.append(" where m.categoryCode=sp.subClass) else '' end as subClassName ");
        sb.append("from sys_product sp where 1=1 ");

        int i = 0;
        Object[] params = new Object[cdt.size()];
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            sb.append(" and " + c.getKey() + "=?");
            params[i] = c.getValue();
            i++;
        }

        SysProductView view = db.queryForBean(sb.toString(), SysProductView.class, params);
        String err = db.getErrCode();
        if (!err.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(err);
        return view;
    }

    //获取产品默认价格
    public List<SysProductView> getProDefaultPrice(Map<String, Object> cdt, String levelCode) {
        int agentId = CloudContextUtils.getAgentId();

        cdt.put("agentId", CloudContextUtils.getAgentId());

        StringBuilder sb = new StringBuilder("SELECT sp.*,");
        sb.append(String.format("(select price1 from sys_product_price spp where sp.productCode=spp.productCode and spp.levelCode='%s' and spp.agentId=%s and spp.chargeId=1 and ISNULL(regType)) AS buyPrice,", levelCode, agentId));
        sb.append(String.format("(select price1 from sys_product_price spp where sp.productCode=spp.productCode and spp.levelCode='%s' and spp.agentId=%s and spp.chargeId=2 and ISNULL(regType)) AS renewPrice ", levelCode, agentId));
        sb.append(" from sys_product sp where 1=1");
        int i = 0;
        Object[] params = new Object[cdt.size()];
        for (Map.Entry<String, Object> c : cdt.entrySet()) {
            sb.append(" and " + c.getKey() + "=?");
            params[i] = c.getValue();
            i++;
        }
        List<SysProductView> list = db.queryForBeans(sb.toString(), SysProductView.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return list;
    }

    //产品编号获取产品信息
    public SysProduct queryProductByProductCode(String productCode) {
        //TODO 产品编号不是唯一，需要修改
        StringBuilder sb = new StringBuilder("select * ");
        sb.append(" from sys_product where 1=1");
        sb.append(" and productCode=? ");
        sb.append(" and agentId=? ");
        SysProduct view = db.queryForBean(sb.toString(), SysProduct.class, new Object[]{productCode, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return view;
    }

    /**
     * 根据字段查询集合
     *
     * @param productClass
     * @return
     */
    public List<SysProduct> queryProductClass(String productClass) {
        String sql = "select * from sys_product where productClass=? and agentId=?";
        List<SysProduct> list = db.queryForBeans(sql, SysProduct.class, new Object[]{productClass, CloudContextUtils.getAgentId()});
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return list;
    }

    //↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓代理和主站数据互通专用↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

    /**
     * 获取产列表（平台与代理数据同步使用）
     *
     * @param productClass 产品类型
     * @param regType      注册商，非域名产品时使用
     * @param agentId      代理商ID  系统为 0
     * @return
     */
    public List<SysProduct> queryAgengtSysProduct(String productClass, String regType, int agentId) {
        StringBuilder sb = new StringBuilder("select * from sys_product where 1=1 ");
        sb.append(" and productClass=? and agentId=? ");

        Object[] params = {productClass, agentId};
        if (!productClass.equals(ProductTypeEnum.DOMAIN.getTag())) {
            sb.append(" and regType=? ");
            params = CObjectTool.objectArrAddValue(params, regType);
        }
        List<SysProduct> list = db.queryForBeans(sb.toString(), SysProduct.class, params);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return list;
    }

    /**
     * 新增产品（平台与代理数据同步使用）
     *
     * @param obj 产品类型
     * @return
     */
    public int addAgentSysProduct(SysProduct obj) {
        String sql = "insert into sys_product(createTime,listId,productClass,productCode,productName," +
                "saleType,status,userGradeSale,remark,subClass," +
                "recommend,regType,agentId,productParam,productDetail) " +
                "values(?,?,?,?,?," +
                "?,?,?,?,?," +
                "?,?,?,?,?);";
        Object[] prm = {new Date(), obj.getListId(), obj.getProductClass(), obj.getProductCode(), obj.getProductName(),
                obj.getSaleType(), obj.getStatus(), obj.getUserGradeSale(), obj.getRemark(), obj.getSubClass(),
                obj.getRecommend(), obj.getRegType(), obj.getAgentId(), obj.getProductParam(), obj.getProductDetail()};

        int ret = db.executeSql(sql, prm);
        String code = db.getErrCode();
        if (!code.equals(SysErr.CODE_SUCCESS)) {
            this.log(db.getMsg());
        }
        this.setErrCode(code);
        return ret;
    }

    /**
     * 删除产品（平台与代理数据同步使用）
     *
     * @param cdt 产品类型
     * @return
     */
    public int delAgentSysProduct(Map<String, Object> cdt) {
        String code = SysErr.SYS_PARAM_ERR;
        int ret = -1;
        do {
            //没有agentId 删除
            if (!cdt.containsKey("agentId")) {
                code = SysErr.SYS_PARAM_ERR;
                break;
            }
            //没有agentId 小于0删除
            if (Integer.valueOf(cdt.get("agentId").toString()) < 0) {
                code = SysErr.SYS_PARAM_ERR;
                break;
            }
            ret = db.deleteRecord("sys_product", cdt);
            code = db.getErrCode();
            if (!code.equals(SysErr.CODE_SUCCESS)) {
                this.log(db.getMsg());
            }
        } while (false);
        this.setErrCode(code);
        return ret;
    }
    //↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑代理和主站数据互通专用↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑

    /**
     * 指定代理获取产品信息
     * @param productClass 产品分类.
     * @param productCode 产品编号.
     * @param agentId 代理ID.
     * @return 产品信息.
     */
    public SysProduct getProductAppointAgent(String productClass, String productCode, Integer agentId) {
        StringBuilder sb = new StringBuilder("select * from sys_product where productClass=? and productCode=? ").
                append(" and agentId=? ");


        Object[] params = new Object[]{productClass, productCode, agentId};

        SysProduct obj = db.queryForBean(sb.toString(), SysProduct.class, params);
        this.setErrCode(db.getErrCode());
        return obj;
    }

    /**
     * 同步产品描述和状态.
     * @param agentId 代理ID.
     */
    public void syncAgentProductDetailAndStatus(Integer agentId) {
        String sql = "update sys_product a set a.productDetail=" +
                "IF(IFNULL(a.productDetail, '')='', " +
                "(SELECT b.productDetail from (select * from sys_product c where c.agentId=0) as b " +
                " where a.productClass=b.productClass and a.regType=b.regType " +
                " and a.productCode=b.productCode and IF(IFNULL(a.subClass, '')='', 1=1, a.subClass=b.subClass) limit 1), a.productDetail), " +

                " a.`status` = IF((SELECT m.status from (select * from sys_product n where n.agentId=0) as m " +
                " where m.productClass=a.productClass and m.productCode=a.productCode and m.regType=a.regType and " +
                " IF(IFNULL(a.subClass, '')='', 1=1, a.subClass=m.subClass) limit 1)=2, 2, a.status) " +
                " where a.agentId=?; ";
        this.db.executeSql(sql, new Object[] {agentId});
    }

    /**
     * 强制更新产品描述.
     * @param productTypeEnum
     * @param productCode
     * @param productDetail
     */
    public void mustUpdateProductDetail (ProductTypeEnum productTypeEnum, String productCode,String subClass,
                                         String productName, String productDetail, String status, Integer recommend) {
        String sql = "update sys_product a set productName=?, a.productDetail=?, status=?, recommend=? where a.productClass=? and a.productCode=? ";
        List<Object> params = new ArrayList<>();
        params.add(productName);
        params.add(productDetail);
        params.add(status);
        params.add(recommend);
        params.add(productTypeEnum.getTag());
        params.add(productCode);
        if (!StrTool.isBlankStr(subClass)) {
            sql += " and a.subClass=? ";
            params.add(subClass);
        }
        sql += " and a.agentId<>0 and " +
                " ( ISNULL((SELECT d.paramValue FROM dic_param d where d.paramName=? and d.agentId=a.agentId)) " +
                " or (SELECT d.paramValue FROM dic_param d where d.paramName=? and d.agentId=a.agentId) = 'Y' ) ";
        params.add(DicParamEnum.DEF_SYNC_PRO_DETAIL.getVal());
        params.add(DicParamEnum.DEF_SYNC_PRO_DETAIL.getVal());
        this.db.executeSql(sql, params);
    }

    /**
     * 同步新增.
     * @param agentId
     * @param productClass
     * @param productCode
     * @param subClass
     */
    public void mustAddPro(Integer agentId, String productClass, String productCode, String subClass, Double diyPremium) {
        String sql;
        if (ProductTypeEnum.DIY.equals(productClass)) {
            sql = "insert into sys_product (productClass, productCode, subClass, productName, recommend, listId, " +
                    " agentId, regType, saleType, status, userGradeSale, createTime, productParam) " +
                    " select productClass, productCode, subClass, productName, recommend, listId, ?, " +
                    "regType, saleType, status, userGradeSale, createTime, concat('{\"premium\":', ?, '}') " +
                    " from sys_product where productClass=? and productCode=? and agentId=0 ";
        } else {
            sql = "insert into sys_product (productClass, productCode, subClass, productName, recommend, listId, " +
                    " agentId, regType, saleType, status, userGradeSale, createTime) select productClass, productCode, " +
                    " subClass, productName, recommend, listId, ?, regType, saleType, status, userGradeSale, createTime " +
                    " from sys_product where productClass=? and productCode=? and agentId=0 ";
        }

        List<Object> params = new ArrayList<>();
        params.add(agentId);
        if (ProductTypeEnum.DIY.equals(productClass)) {
            params.add(diyPremium);
        }
        params.add(productClass);
        params.add(productCode);

        if (!StrTool.isBlankStr(subClass)) {
            sql += " and subClass=? ";
            params.add(subClass);
        }

        this.db.executeSql(sql, params);
    }

    /**
     * 同步删除.
     * @param productClass
     * @param productCode
     * @param subClass
     */
    public void mustDelPro(String productClass, String productCode, String subClass) {
        String sql = "delete from sys_product where productClass=? and productCode=? ";
        List<Object> params = new ArrayList<>();
        params.add(productClass);
        params.add(productCode);
        if (!StrTool.isBlankStr(subClass)) {
            sql += " and subClass=? ";
            params.add(subClass);
        }
        sql += " and agentId<>0 and " +
                " ( ISNULL((SELECT d.paramValue FROM dic_param d where d.paramName=? and d.agentId=sys_product.agentId)) " +
                " or (SELECT d.paramValue FROM dic_param d where d.paramName=? and d.agentId=sys_product.agentId) = 'Y' ) ";
        params.add(DicParamEnum.DEF_SYNC_PRO_DETAIL.getVal());
        params.add(DicParamEnum.DEF_SYNC_PRO_DETAIL.getVal());
        this.db.executeSql(sql, params);
    }

    /**
     * 当前代理更新所有产品信息和主站一致.
     */
    public void agentUpdateProductDetailAll() {
        String sql = "update sys_product a set a.productDetail=(SELECT b.productDetail from " +
                " (select * from sys_product c where c.agentId=0) as b where a.productClass=b.productClass " +
                " and a.regType=b.regType and a.productCode=b.productCode and " +
                "IF(IFNULL(a.subClass, '')='', 1=1, a.subClass=b.subClass) limit 1)  where a.agentId=? ";
        this.db.executeSql(sql, new Object[] {CloudContextUtils.getAgentId()});
    }

    /**
     * 代理自定义产品价格溢价率.
     * @param premium
     */
    public void setDiyProductPrice(Double premium) {
        String sql = "update sys_product set productParam=CONCAT(" +
                " REPLACE(productParam, SUBSTR(productParam, LOCATE('\"premium\"', productParam)), ''), " +
                " '\"premium\":" + String.valueOf(premium) + "', " +
                " IF(IFNULL(SUBSTR(productParam, LOCATE(\",\", productParam, LOCATE('\"premium\"', productParam))), '')='', " +
                "       '}', SUBSTR(productParam, LOCATE(\",\", productParam, LOCATE('\"premium\"', productParam))))) " +
                " where productClass=? and agentId=? ";
        List<Object> params = new ArrayList<>();
        params.add(ProductTypeEnum.DIY.getTag());
        params.add(CloudContextUtils.getAgentId());
        this.db.executeSql(sql, params);
    }
}